Imports ADOConnection

Public Class CSysVar
    Private _ten As String
    Private _giatri As String

    Public Property Ten() As String
        Get
            Return _ten
        End Get
        Set(ByVal Value As String)
            _ten = Value
        End Set
    End Property

    Public Property GiaTri() As String
        Get
            Return _giatri
        End Get
        Set(ByVal Value As String)
            _giatri = Value
        End Set
    End Property
End Class

Public Class CSysVars
    Public Shared Function GetGiaTri(ByVal pTen As String) As String
        Dim KQ As String = ""
        Dim strSQL As String
        strSQL = "SELECT * FROM tblSysVar where ten = '" & pTen & "' and user_id = " + g_User.User_ID.ToString()
        Dim objReader As SqlClient.SqlDataReader = Nothing
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            If objReader.Read Then
                KQ = objReader.GetString(2)
            End If
        Catch ex As System.Exception
            Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            BaseDB.CloseConnect()
        End Try
        Return KQ
    End Function

    Private Shared Function Insert(ByVal pTen As String, ByVal pGiaTri As String) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim sInsert As String = "INSERT INTO tblSysVar" & _
                    " (user_id, ten, giatri) VALUES(" & _
        objDBUtility.GetParamPlaceHolder("user_id", True) & _
        objDBUtility.GetParamPlaceHolder("ten", True) & _
        objDBUtility.GetParamPlaceHolder("giatri", False) & _
        ")"

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("user_id", DbType.String, g_User.User_ID.ToString(), g_User.User_ID.ToString.Length()))
                .Parameters.Add(objDBUtility.CreateParameter("ten", DbType.String, pTen, pTen.Length))
                .Parameters.Add(objDBUtility.CreateParameter("giatri", DbType.String, pGiaTri, pGiaTri.Length))
                lRowsAffected = .ExecuteNonQuery()
            End With
            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "HopDong_ID", "tblHopDong")
            objTransaction.Commit()
        Catch ex As System.Exception
            objTransaction.Rollback()
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Private Shared Function Update(ByVal pTen As String, ByVal pGiaTri As String) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility
        Dim lRowsAffected As Long = 0

        Dim sUpdate As String = "UPDATE tblSysVar SET"
        sUpdate &= " giatri = '" & pGiaTri + "'"
        sUpdate &= " WHERE ten = '" & pTen + "'"
        sUpdate &= " and user_id =" + g_User.User_ID.ToString()

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sUpdate)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction

        Try
            With objCommand
                .Transaction = objTransaction
                lRowsAffected = .ExecuteNonQuery()
            End With
            objTransaction.Commit()
        Catch ex As System.Exception
            objTransaction.Rollback()
        Finally
            objConnection.Close()
        End Try
        Return lRowsAffected
    End Function

    Public Shared Sub SetGiaTri(ByVal pTen As String, ByVal pGiaTri As String)
        If Insert(pTen, pGiaTri) = 0 Then
            Update(pTen, pGiaTri)
        End If
    End Sub

    Public Shared Function GetGiaTriDateTime(ByVal pTen As String) As Date
        Dim KQ As Date = DateNull()
        Dim strSQL As String
        strSQL = "SELECT * FROM tblSysVar where ten = '" & pTen & "' and user_id = " + g_User.User_ID.ToString()
        Dim objReader As SqlClient.SqlDataReader = Nothing
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            If objReader.Read Then
                Try
                    KQ = objReader.GetDateTime(3)
                Catch ex As Exception
                    KQ = DateNull()
                End Try
            End If
        Catch ex As System.Exception
            Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            BaseDB.CloseConnect()
        End Try
        Return KQ
    End Function

    Private Shared Function InsertDateTime(ByVal pTen As String, ByVal pGiaTri As String) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim sInsert As String = "INSERT INTO tblSysVar" & _
                    " (user_id, ten, giatridatetime) VALUES(" & _
        objDBUtility.GetParamPlaceHolder("user_id", True) & _
        objDBUtility.GetParamPlaceHolder("ten", True) & _
        objDBUtility.GetParamPlaceHolder("giatridatetime", False) & _
        ")"

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("user_id", DbType.Int16, g_User.User_ID, 0))
                .Parameters.Add(objDBUtility.CreateParameter("ten", DbType.String, pTen, pTen.Length))
                .Parameters.Add(objDBUtility.CreateParameter("giatridatetime", DbType.Date, GetDateParam(pGiaTri), 0))
                lRowsAffected = .ExecuteNonQuery()
            End With
            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "HopDong_ID", "tblHopDong")
            objTransaction.Commit()
        Catch ex As System.Exception
            objTransaction.Rollback()
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Private Shared Function UpdateDateTime(ByVal pTen As String, ByVal pGiaTri As String) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility
        Dim lRowsAffected As Long = 0

        Dim strSQL As String = "UPDATE tblSysVar SET"
        strSQL &= " giatridatetime = " & objDBUtility.GetParamPlaceHolder("giatridatetime", False)
        strSQL &= " WHERE ten = " & objDBUtility.GetParamPlaceHolder("ten", False)
        strSQL &= " and user_id = " & objDBUtility.GetParamPlaceHolder("user_id", False)

        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("giatridatetime", DbType.Date, GetDateParam(pGiaTri), 0))
                .Parameters.Add(objDBUtility.CreateParameter("ten", DbType.String, pTen, pTen.Length))
                .Parameters.Add(objDBUtility.CreateParameter("user_id", DbType.Int16, g_User.User_ID, 0))
                lRowsAffected = .ExecuteNonQuery()
                objTransaction.Commit()
            End With
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return lRowsAffected
    End Function

    Public Shared Sub SetGiaTriDateTime(ByVal pTen As String, ByVal pGiaTri As String)
        If InsertDateTime(pTen, pGiaTri) = 0 Then
            UpdateDateTime(pTen, pGiaTri)
        End If
    End Sub
End Class
